import json
import os
import time
from typing import Dict, Any
import pyodbc
import requests
from datetime import datetime

from logger import log
from settings import settings


def convert_weight(value):
    if not value:
        return 0.0
    try:
        return round(float(value) / 1000, 2)
    except (ValueError, TypeError):
        return 0.0


# --- 配置区 ---
CONFIG_FILE = 'last.json'


def load_config() -> Dict[str, Any]:
    """加载配置文件"""
    if not os.path.exists(CONFIG_FILE):
        default_config = {
            'last_no': '20251020001'
        }
        with open(CONFIG_FILE, 'w') as f:
            json.dump(default_config, f, indent=4)
        return default_config

    with open(CONFIG_FILE, 'r') as f:
        return json.load(f)


def save_config(config: Dict[str, Any]) -> None:
    """保存配置文件"""
    with open(CONFIG_FILE, 'w') as f:
        json.dump(config, f, indent=4)


def choose_sql_driver():
    try:
        drivers = list(pyodbc.drivers())
        preferred = [
            'ODBC Driver 18 for SQL Server',
            'ODBC Driver 17 for SQL Server',
            'SQL Server Native Client 11.0',
            'SQL Server Native Client 10.0',
            'SQL Server',
        ]
        for name in preferred:
            if name in drivers:
                return '{' + name + '}'
        log.warn(f"未找到预期的SQL Server ODBC驱动，已安装: {drivers}")
        return '{SQL Server}'
    except Exception as e:
        log.warn(f"检测ODBC驱动异常: {e}")
        return '{SQL Server}'


def build_connection_string(driver: str, server: str, database: str, username: str, password: str) -> str:
    # 针对不同驱动生成兼容的连接字符串
    parts = [
        f"DRIVER={driver}",
        f"SERVER={server}",
        f"DATABASE={database}",
        f"UID={username}",
        f"PWD={password}",
    ]
    dname = driver.strip('{}')
    if ('ODBC Driver' in dname) or ('Native Client' in dname):
        parts.append('Encrypt=yes')
        parts.append('TrustServerCertificate=yes')
    else:
        # 旧版 'SQL Server' 驱动不识别 TrustServerCertificate
        parts.append('Trusted_Connection=no')
    return ';'.join(parts) + ';'


def fetch_data_from_sqlserver(connection_string, query):
    """
    从SQL Server数据库获取数据
    """
    try:
        # 建立数据库连接
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()
        # 执行查询
        cursor.execute(query)
        # 获取列名
        columns = [column[0] for column in cursor.description]
        # 获取数据并转换为字典列表
        data = []
        for row in cursor.fetchall():
            row_data = {}
            for i, col in enumerate(columns):
                # 处理datetime对象，使其可JSON序列化
                if isinstance(row[i], datetime):
                    row_data[col] = row[i].isoformat()
                elif isinstance(row[i], str):
                    # 去除nchar字段的填充空格
                    row_data[col] = row[i].strip()
                else:
                    row_data[col] = row[i]
            data.append(row_data)
        return data
    except Exception as e:
        log.error(f"从SQL Server获取数据时出错: {e}")
        try:
            log.error(f"已安装ODBC驱动: {pyodbc.drivers()}")
        except Exception:
            pass
        if '18456' in str(e):
            log.error('登录失败(18456): 请检查用户名/密码是否正确，或SQL Server是否启用SQL身份验证，并确认该用户有访问数据库的权限。')
        if '无效的连接字符串属性' in str(e):
            log.error('提示无效连接属性: 已尝试为旧版驱动移除不兼容属性，请确认所选驱动是否正确。')
        return None
    finally:
        if 'conn' in locals():
            conn.close()


def fetch_new_records(last_no: str) -> list:
    log.info("开始获取数据...")
    # 配置数据库连接
    server = 'OEM'
    database = 'BaoDeLoadOut'
    username = 'hlsk'
    password = 'hlsk1234'
    driver = choose_sql_driver()  # 自动选择可用驱动
    log.info(f"使用ODBC驱动: {driver} | SERVER: {server} | DATABASE: {database} | UID: {username}")

    connection_string = build_connection_string(driver, server, database, username, password)
    log.info(f"连接字符串: {connection_string}")
    # SQL查询
    query = f"""
SELECT
    [装车任务号],
    [序号],
    [顺序位置],
    [车号],
    [车型],
    [额定装载量],
    [计划装载量],
    [实际装载量],
    [偏差],
    [累计偏差],
    [装车日期],
    [装车时间],
    [状态],
    [煤种],
    [收货单位],
    [到站],
    [合同号],
    [车皮总数],
    [操作员],
    [其他],
    (
        SELECT TOP 1 MAX(时间)
        FROM [BaoDeLoadOut].[dbo].[来车信息表2]
        WHERE [装车任务号] = zc.[装车任务号]
    ) AS [来车时间]
FROM
    [BaoDeLoadOut].[dbo].[装车信息表2] zc
WHERE
    zc.[装车任务号] = (
        SELECT MIN(t.[装车任务号])
        FROM [BaoDeLoadOut].[dbo].[装车信息表2] t
        WHERE t.[装车任务号] > '{last_no}'
    )
ORDER BY
    [顺序位置];
    """
    return fetch_data_from_sqlserver(connection_string, query)


def convert_to_train_all(db_record: Dict[str, Any]) -> (bool, Dict[str, Any]):
    if db_record[0]['状态'] == '待装' and db_record[0]['实际装载量'] == None:
        return False, None
    nodes = []
    for record in db_record:
        arrive_time_val = record.get('来车时间')
        if isinstance(arrive_time_val, str):
            s = arrive_time_val.strip().replace('T', ' ')
            s = s.split('.')[0]  # 去掉可能的毫秒
            if '/' in s:
                s = s.replace('/', '-')  # 统一日期分隔符
            try:
                dt = datetime.strptime(s, '%Y-%m-%d %H:%M:%S')
                arrive_time = dt.strftime('%Y-%m-%d %H:%M:%S')
            except Exception:
                arrive_time = s[:19]
        else:
            arrive_time = (
                arrive_time_val.strftime('%Y-%m-%d %H:%M:%S') if arrive_time_val else None
            )

        nodes.append({
            "trainCode": record.get('装车任务号'),
            "code": record.get('装车任务号') + record.get('顺序位置'),
            "no": record.get('车号').strip()[-7:],
            "carType": record.get('车型'),
            "weight": convert_weight(record.get('实际装载量')),
            "time": arrive_time,
            "standardTareWeight": convert_weight(record.get('额定装载量')),
            "maxNetWeight": convert_weight(record.get('额定装载量')),
            "diffWeight": convert_weight(record.get('偏差')),
            "sort": int(record.get('顺序位置')),
            "state": record.get('状态'),
        })

    # 规范化整体时间（来车时间）
    start_time_val = db_record[0].get('来车时间')
    if isinstance(start_time_val, str):
        s = start_time_val.strip().replace('T', ' ')
        s = s.split('.')[0]
        if '/' in s:
            s = s.replace('/', '-')
        try:
            dt = datetime.strptime(s, '%Y-%m-%d %H:%M:%S')
            start_time = dt.strftime('%Y-%m-%d %H:%M:%S')
        except Exception:
            start_time = s[:19]
    else:
        start_time = (
            start_time_val.strftime('%Y-%m-%d %H:%M:%S') if start_time_val else ""
        )

    # 解析 endTime：最后一条记录的装车日期+装车时间，字段类型为 nchar 需去空格
    last = db_record[-1]
    date_s = (last.get('装车日期') or '').strip()
    time_s = (last.get('装车时间') or '').strip()
    end_time = ""
    if date_s and time_s:
        try:
            end_dt = datetime.strptime(f"{date_s} {time_s}", "%m/%d/%Y %I:%M:%S %p")
            end_time = end_dt.strftime('%Y-%m-%d %H:%M:%S')
        except ValueError:
            try:
                end_dt = datetime.strptime(f"{date_s} {time_s}", "%m/%d/%Y %H:%M:%S")
                end_time = end_dt.strftime('%Y-%m-%d %H:%M:%S')
            except ValueError:
                end_time = f"{date_s} {time_s}"  # 保底返回原始拼接

    data = {
        "platformId": settings.platformId,
        "platformName": settings.platformName,
        "name": f"{nodes[0].get('trainCode')}-xxx-万镕-黄骅港",
        "code": nodes[0].get('trainCode'),
        "time": start_time,
        "direction": "Leave",
        "totalNode": len(db_record),
        "startTime": start_time,
        "endTime": end_time,
        "senderName": "万镕",
        "stationName": "黄骅港",
        "totalWeight": round(sum([node.get('weight') for node in nodes]), 2),
        "totalMaxWeight": round(sum([node.get('maxNetWeight') for node in nodes]), 2),
        "totalDiffWeight": round(sum([node.get('diffWeight') for node in nodes]), 2),
        "firstNo": nodes[0].get('no'),
        "lastNo": nodes[-1].get('no'),
        "type": "db",
        "nodes": nodes
    }
    return db_record[-1].get('状态') == '已装', data


def send_to_api(data: Dict[str, Any]) -> bool:
    """发送数据到API"""
    headers = {'Content-Type': 'application/json'}
    try:
        if not settings.server_url:
            log.warn("server_url 未配置，无法发送到服务器")
            return False
        url = settings.server_url + "/depatch/internal/train/trainAll"
        log.info(f"POST -> {url}")
        response = requests.post(url, data=json.dumps(data), headers=headers)
        if response.status_code == 200:
            response_json = response.json()
            if response_json.get('code') == '0000':
                log.info(f"成功发送记录: {data.get('code')}")
                return True
        log.warn(f"发送失败: {response.status_code}, {response.text}")
        return False
    except Exception as e:
        log.warn(f"API请求异常: {str(e)}")
        return False


def collection():
    # 加载配置
    config = load_config()
    log.info(f"当前配置: 最后处理ID={config['last_no']}")

    # 获取新记录
    records = fetch_new_records(config['last_no'])
    if records is None:
        log.error("数据库连接失败或ODBC驱动缺失，无法获取记录")
        return
    if not records:
        log.info("没有需要处理的新记录")
        return

    log.info(f"找到 {len(records)} 条新记录")

    over, data =  convert_to_train_all(records)
    if data is None:
        log.info("转换数据失败，实际装载量为空")
        return

    # 发送到相应接口
    success = send_to_api(data)

    if over and success:
        # 更新最后处理的ID
        config['last_no'] = data.get('code')
        # 保存配置
        save_config(config)
        log.info("配置已更新")


def main():
    """主函数"""
    log.info("开始运行")
    while True:
        try:
            collection()
        except Exception as e:
            log.error(f"collection异常: {str(e)}", e)
        time.sleep(300)


if __name__ == '__main__':
    settings.init(local=True)
    main()
